{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "25088142-188e-49bf-87e1-0b99e59e8ecc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://hbu:********@127.0.0.1:2345/hbu\n",
      "环境变量加载完成！\n",
      "数据库连接引擎创建成功！\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "# 加载环境变量\n",
    "load_dotenv()\n",
    "\n",
    "# 从环境变量获取数据库连接信息，如果不存在则使用默认值\n",
    "driver=os.getenv('DRIVER', 'postgresql')\n",
    "user=os.getenv('PGUSER', None)\n",
    "password=os.getenv('PGPASSWORD', None)\n",
    "host=os.getenv('PGHOST', None)\n",
    "port=os.getenv('PGPORT', None)\n",
    "database=os.getenv('PGDATABASE', 'postgres')\n",
    "\n",
    "# schema is used for postgres, similiar with database level in MySQL\n",
    "schema=os.getenv('SCHEMA',\"public\")\n",
    "\n",
    "DATABASE_URL = f\"{driver}://{user}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "if user is not None and password is not None:\n",
    "    print(f\"{driver}://{user}:********@{host}:{port}/{database}\")\n",
    "else:\n",
    "    print('非法的数据库连接URL')\n",
    "    sys.exit(1)\n",
    "print('环境变量加载完成！')\n",
    "\n",
    "# 创建数据库连接引擎\n",
    "try:\n",
    "    engine = create_engine(DATABASE_URL)\n",
    "    print('数据库连接引擎创建成功！')\n",
    "except Exception as e:\n",
    "    print(f'创建数据库连接引擎失败: {e}')\n",
    "    raise\n",
    "# 从PostgreSQL数据库读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "167ac3be-617f-45f9-9712-e2a916ad6903",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "成功读取数据，共545条记录\n",
      "                  Timestamp  Your name Your gender  Your age  \\\n",
      "0 2019-07-04 05:18:07+00:00    Parkavi      Female  19 to 25   \n",
      "1 2019-07-04 05:21:22+00:00   Nithilaa      Female  19 to 25   \n",
      "2 2019-07-04 05:26:28+00:00  Karunya v      Female  15 to 18   \n",
      "3 2019-07-04 11:13:35+00:00    Anusha       Female  15 to 18   \n",
      "4 2019-07-04 11:14:29+00:00   Nikkitha      Female  19 to 25   \n",
      "\n",
      "   How important is exercise to you ?  \\\n",
      "0                                 2.0   \n",
      "1                                 4.0   \n",
      "2                                 3.0   \n",
      "3                                 4.0   \n",
      "4                                 3.0   \n",
      "\n",
      "  How do you describe your current level-516f81c2 How often do you exercise?  \\\n",
      "0                                            Good                      Never   \n",
      "1                                       Very good                      Never   \n",
      "2                                            Good        1 to 2 times a week   \n",
      "3                                            Good        3 to 4 times a week   \n",
      "4                                           Unfit                      Never   \n",
      "\n",
      "  What barriers, if any, prevent you from-23b4e9bc  \\\n",
      "0  I don't have enough time;I can't stay motivated   \n",
      "1   I don't have enough time;I'll become too tired   \n",
      "2                           I can't stay motivated   \n",
      "3                         I don't have enough time   \n",
      "4                           I can't stay motivated   \n",
      "\n",
      "  What form(s) of exercise do you current-d8c5628a  \\\n",
      "0                          I don't really exercise   \n",
      "1                      Walking or jogging;Swimming   \n",
      "2                               Walking or jogging   \n",
      "3           Walking or jogging;Gym;Lifting weights   \n",
      "4                          I don't really exercise   \n",
      "\n",
      "  Do you exercise ___________ ?  \\\n",
      "0       I don't really exercise   \n",
      "1                  With a group   \n",
      "2                         Alone   \n",
      "3                         Alone   \n",
      "4       I don't really exercise   \n",
      "\n",
      "  What time if the day do you prefer to exercise?  \\\n",
      "0                                   Early morning   \n",
      "1                                   Early morning   \n",
      "2                                   Early morning   \n",
      "3                                         Evening   \n",
      "4                                         Evening   \n",
      "\n",
      "  How long do you spend exercising per day ?  \\\n",
      "0                    I don't really exercise   \n",
      "1                    I don't really exercise   \n",
      "2                                 30 minutes   \n",
      "3                                     1 hour   \n",
      "4                    I don't really exercise   \n",
      "\n",
      "  Would you say you eat a healthy balanced diet ?  \\\n",
      "0                                      Not always   \n",
      "1                                      Not always   \n",
      "2                                      Not always   \n",
      "3                                             Yes   \n",
      "4                                             Yes   \n",
      "\n",
      "    What prevents you from eating a healthy-d6c96f4b  \\\n",
      "0  Ease of access to fast food;Temptation and cra...   \n",
      "1  Ease of access to fast food;Temptation and cra...   \n",
      "2                            Temptation and cravings   \n",
      "3                            Temptation and cravings   \n",
      "4  Ease of access to fast food;Temptation and cra...   \n",
      "\n",
      "   How healthy do you consider yourself?  \\\n",
      "0                                    3.0   \n",
      "1                                    4.0   \n",
      "2                                    4.0   \n",
      "3                                    4.0   \n",
      "4                                    4.0   \n",
      "\n",
      "   Have you ever recommended your friends-6766e84c  \\\n",
      "0                                             True   \n",
      "1                                             True   \n",
      "2                                             True   \n",
      "3                                             True   \n",
      "4                                             True   \n",
      "\n",
      "   Have you ever purchased a fitness equipment?  \\\n",
      "0                                         False   \n",
      "1                                         False   \n",
      "2                                          True   \n",
      "3                                         False   \n",
      "4                                         False   \n",
      "\n",
      "            What motivates you to exercise?-07987186  \n",
      "0  I'm sorry ... I'm not really interested in exe...  \n",
      "1  I want to be fit;I want to be flexible;I want ...  \n",
      "2                                   I want to be fit  \n",
      "3             I want to be fit;I want to lose weight  \n",
      "4                                   I want to be fit  \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/mh/l6_b7c0x7m3bq41r5m25snqc0000gn/T/ipykernel_6677/2398387690.py:23: FutureWarning: DataFrame.applymap has been deprecated. Use DataFrame.map instead.\n",
      "  df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)\n"
     ]
    }
   ],
   "source": [
    "#import pandas as pd\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "import pickle\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "import xgboost as xgb\n",
    "\n",
    "# 加载数据集\n",
    "# df = pd.read_csv('fitness analysis.csv')\n",
    "try:\n",
    "    # 查询public模式下的patient_data表\n",
    "    query = f'SELECT * FROM public.\"fitness analysis\"'\n",
    "    df = pd.read_sql(query, engine)\n",
    "    print(f'成功读取数据，共{len(df)}条记录')\n",
    "except Exception as e:\n",
    "    print(f'读取数据失败: {e}')\n",
    "df.drop(columns=['id'],inplace=True)\n",
    "\n",
    "# 显示前五行数据\n",
    "print(df.head())\n",
    "\n",
    "# 去除所有字符串字段的前后空格\n",
    "df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)\n",
    "\n",
    "# 检查和清理列名\n",
    "df.columns = df.columns.str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "070e6192-3ad0-418c-99ab-c33b2793bf97",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Timestamp', 'Your name', 'Your gender', 'Your age',\n",
       "       'How important is exercise to you ?',\n",
       "       'How do you describe your current level-516f81c2',\n",
       "       'How often do you exercise?',\n",
       "       'What barriers, if any, prevent you from-23b4e9bc',\n",
       "       'What form(s) of exercise do you current-d8c5628a',\n",
       "       'Do you exercise ___________ ?',\n",
       "       'What time if the day do you prefer to exercise?',\n",
       "       'How long do you spend exercising per day ?',\n",
       "       'Would you say you eat a healthy balanced diet ?',\n",
       "       'What prevents you from eating a healthy-d6c96f4b',\n",
       "       'How healthy do you consider yourself?',\n",
       "       'Have you ever recommended your friends-6766e84c',\n",
       "       'Have you ever purchased a fitness equipment?',\n",
       "       'What motivates you to exercise?-07987186'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f55c4b34-5de9-461a-a2c5-bc657f042f3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# 选择相关特征进行建模\n",
    "X = df[['Your gender', 'How important is exercise to you ?', 'How healthy do you consider yourself?']]\n",
    "X = pd.get_dummies(X)  # 将分类变量转为数值变量\n",
    "\n",
    "# 将年龄段转为数值变量\n",
    "y = df['Your age'].apply(lambda x: int(x.split(' ')[0]))  # 假设年龄段为整数\n",
    "\n",
    "# 将数据集划分为训练集和测试集（测试集占比20%）\n",
    "X_train, X_test, y_train, y_test = train_test_split(X,y, test_size=0.2, random_state=42)\n",
    "\n",
    "# 创建随机森林回归模型（创建的决策树的数量为100）\n",
    "rf_model = RandomForestRegressor(n_estimators=100, random_state=42)\n",
    "# 训练随机森林回归模型\n",
    "rf_model.fit(X_train, y_train)\n",
    "\n",
    "# 保存训练好的模型\n",
    "with open('2.2.3_model.pkl', 'wb') as model_file:\n",
    "    pickle.dump(rf_model, model_file)\n",
    "\n",
    "# 进行结果预测\n",
    "y_pred = rf_model.predict(X_test)\n",
    "results_df = pd.DataFrame(y_pred, columns=['预测结果'])\n",
    "results_df.to_csv('2.2.3_results.txt', index=False)\n",
    "\n",
    "# 使用测试工具对模型进行测试，并记录测试结果\n",
    "train_score = rf_model.score(X_train,y_train)   #训练集分数\n",
    "test_score = rf_model.score(X_test, y_test)    #测试集分数\n",
    "mse = mean_squared_error(y_test, y_pred)  #均方误差\n",
    "r2 = r2_score(y_test, y_pred)  #决定系数\n",
    "with open('2.2.3_report.txt', 'w') as report_file:\n",
    "    report_file.write(f'训练集得分: {train_score}\\n')\n",
    "    report_file.write(f'测试集得分: {test_score}\\n')\n",
    "    report_file.write(f'均方误差(MSE): {mse}\\n')\n",
    "    report_file.write(f'决定系数(R^2): {r2}\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f589d20d-5173-40ac-9c2b-0215e62c02c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.09219954290443844"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "de3c1194",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# 运用工具分析算法中错误案例产生的原因并进行纠正\n",
    "# 初始化XGBoost回归模型（构建100棵树）\n",
    "xgb_model = xgb.XGBRegressor(n_estimators=100, random_state=42)\n",
    "# 训练XGBoost回归模型\n",
    "xgb_model.fit(X_train, y_train)\n",
    "# 使用XGBoost回归模型在测试集上进行结果预测\n",
    "y_pred_xgb = xgb_model.predict(X_test)\n",
    "\n",
    "results_df_xgb = pd.DataFrame(y_pred_xgb, columns=['预测结果'])\n",
    "results_df_xgb.to_csv('2.2.3_results_xgb.txt', index=False)\n",
    "\n",
    "with open('2.2.3_report_xgb.txt', 'w') as xgb_report_file:\n",
    "    xgb_report_file.write(f'XGBoost训练集得分: {xgb_model.score(X_train,y_train) }\\n')\n",
    "    xgb_report_file.write(f'XGBoost测试集得分: {xgb_model.score(X_test, y_test) }\\n')\n",
    "    xgb_report_file.write(f'XGBoost均方误差(MSE): {mean_squared_error(y_test, y_pred_xgb)}\\n')\n",
    "    xgb_report_file.write(f'XGBoost决定系数(R^2): {r2_score(y_test, y_pred_xgb)}\\n')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "87d79ede",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.09105539321899414"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r2_score(y_test, y_pred_xgb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "706488fd-d90e-447d-82cb-23c73f8d6f97",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
